﻿using CPPEI.Coolzon.Data;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Security;
using CPPEI.Coolzon.Common;

namespace CPPEI.Coolzon.DataProvider.SQL
{
    public class SqlDataProvider : IDataProvider
    {
        private readonly string connSql = "{0};uid={1};pwd={2};";
        public DataResult QueryData(Metadata.DataSource dataSource, Metadata.DataSet dataSet, List<QueryParameter> parameters)
        {
            string strParam = string.Empty;
            if (parameters != null && parameters.Count > 0)
            {
                foreach (QueryParameter qp in parameters)
                {
                    strParam += string.Format("{0}:{1};", qp.Name, qp.ValueString);
                }
            }

            DataResult retDr = null;
            long beginTicks = DateTime.Now.Ticks;
            try
            {
                //Logger.Info("SqlDataProvider", "DataSource:" + dataSource.Name + ",DataSet:" + dataSet.Name);

                if (dataSource.Type == "Code")
                {
                    return InfoDemoData.QueryData(dataSource, dataSet, parameters);
                }
                else if (dataSource.Type.Equals("Pipe"))
                {
                    return PipeNetworkDemoData.QueryData(dataSource, dataSet, parameters);
                }

                #region 临时随机数测试
                //临时随机数测试
                if (!string.IsNullOrWhiteSpace(dataSet.ValueString) && dataSet.ValueString.Equals("randomTest"))
                {
                    return RandomData();
                }
                else if (!string.IsNullOrWhiteSpace(dataSet.ValueString) && dataSet.ValueString.Equals("randomTest2"))
                {
                    return RandomData2();
                }
                else if (!string.IsNullOrWhiteSpace(dataSet.ValueString) && dataSet.ValueString.Equals("randomTest3"))
                {
                    return RandomData3(parameters);
                }
                else if (!string.IsNullOrWhiteSpace(dataSet.ValueString) && dataSet.ValueString.Equals("FixTest1"))
                {
                    return FixTest(parameters);
                }
                else if (!string.IsNullOrWhiteSpace(dataSet.ValueString) && dataSet.ValueString.Equals("RandomDataA26Left2"))
                {
                    return RandomDataA26Left2(parameters);
                }
                else if (!string.IsNullOrWhiteSpace(dataSet.ValueString) && dataSet.ValueString.Equals("RandomDataA26Left2Dowm"))
                {
                    return RandomDataA26Left2Dowm(parameters);
                }
                else if (!string.IsNullOrWhiteSpace(dataSet.ValueString) && dataSet.ValueString.Equals("RandomDataA26Left2Up"))
                {
                    return RandomDataA26Left2Up(parameters);
                }
                else if (!string.IsNullOrWhiteSpace(dataSet.ValueString) && dataSet.ValueString.Equals("RandomDataA26A3NameData"))
                {
                    return RandomDataA26A3NameData(parameters);
                }
                else if (!string.IsNullOrWhiteSpace(dataSet.ValueString) && dataSet.ValueString.Equals("RandomDataA26A4NameData"))
                {
                    return RandomDataA26A4NameData(parameters);
                }
                else if (!string.IsNullOrWhiteSpace(dataSet.ValueString) && dataSet.ValueString.Equals("RandomDataC42S3S42A2NameData"))
                {
                    return RandomDataC42S3S42A2NameData(parameters);
                }
                
                #endregion

                if (string.IsNullOrWhiteSpace(dataSet.ValueString))
                {
                    return retDr;
                }

                using (SqlConnection conn = new SqlConnection(string.Format(connSql, dataSource.Address, dataSource.Authentication.User, dataSource.Authentication.Password, dataSource.Authentication.Mode)))
                {
                    using (SqlCommand cmd = new SqlCommand(dataSet.ValueString, conn))
                    {
                        cmd.CommandTimeout = 300;

                        //设置类型为存储过程，默认为Sql语句
                        if (dataSet.Type.Equals("StoredProcedure"))
                        {
                            cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        }
                        //添加参数
                        if (parameters != null && parameters.Count > 0)
                        {
                            foreach (var param in parameters)
                            {
                                SqlParameter sqlParam = new SqlParameter(param.Name, param.ValueString);
                                if (param.Type.ToUpper().Equals("OUT"))
                                {
                                    sqlParam.Direction = System.Data.ParameterDirection.Output;
                                }
                                cmd.Parameters.Add(sqlParam);
                            }
                        }
                        using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                        {
                            conn.Open();

                            retDr = new DataResult();
                            retDr.Name = dataSet.Name;
                            retDr.DataSeries = new List<DataSeries>();

                            System.Data.DataSet dset = new System.Data.DataSet();
                            sda.Fill(dset);
                            if (dset != null && dset.Tables.Count > 0 && dset.Tables[0].Rows.Count > 0)
                            {
                                //Logger.Info("SqlDataProvider", "DataSource:" + dataSource.Name + ",DataSet:" + dataSet.Name + "。获取数据集成功。");
                                foreach (System.Data.DataRow dr in dset.Tables[0].Rows)
                                { //相同的DimCode在同一个DataSeries里
                                    DataSeries ds = retDr.DataSeries.Find(dataSeries => dataSeries.Id.Equals(dr["DimCode"].ToString()));
                                    if (ds != null)
                                    {
                                        if (ds.DataItems == null)
                                        {
                                            ds.DataItems = new List<DataItem>();
                                        }
                                        DataItem di = new DataItem();
                                        di.Id = dr["ItemCode"].ToString();
                                        di.Name = dr["ItemName"].ToString();
                                        di.Value = dr["ItemValue"].ToString();
                                        ds.DataItems.Add(di);
                                    }
                                    else
                                    {
                                        ds = new DataSeries();
                                        ds.Id = dr["DimCode"].ToString();
                                        ds.Name = dr["DimName"].ToString();
                                        ds.DataItems = new List<DataItem>();
                                        DataItem di = new DataItem();
                                        di.Id = dr["ItemCode"].ToString();
                                        di.Name = dr["ItemName"].ToString();
                                        di.Value = dr["ItemValue"].ToString();
                                        ds.DataItems.Add(di);

                                        retDr.DataSeries.Add(ds);
                                    }
                                }
                            }
                            else
                            {
                                Logger.WarningToTag("SqlDataProvider", "DataSource:{0},DataSet:{1},Parameters:{2},获取数据集失败或为空。", dataSource.Name, dataSet.Name, strParam);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.ErrorToTag("SqlDataProvider", "DataSource:{0},DataSet:{1},Parameters:{2}。获取数据集失败,{3}", dataSource.Name, dataSet.Name, strParam, ex.Message);
                throw ex;
            }
            long endTicks = DateTime.Now.Ticks;
            long costTicks = (endTicks - beginTicks) / 10000;
            if (costTicks > 1000)
            {
                Logger.WarningToTag("SqlDataProvider", "DataSource:{0},DataSet:{1},Parameters:{3}获取数据集完成,耗时:{2}ms", dataSource.Name, dataSet.Name, costTicks, strParam);
            }
            return retDr;
        }


        private DataResult FixTest(List<QueryParameter> parameters)
        {
            int month = int.Parse(parameters.Find(p => p.Name.Equals("month")).ValueString);
            DataResult retDr = new DataResult();
            retDr.Name = "random";
            retDr.DataSeries = new List<DataSeries>();
            for (int j = 1; j <= month; j++)
            {
                DataSeries ds = new DataSeries();
                ds.DataItems = new List<DataItem>();
                ds.Id = ((char)(64 + j)).ToString();
                ds.Name = "测试数据" + j;
                for (int i = 1; i <= 4; i++)
                {
                    DataItem di = new DataItem();
                    di.Id = i.ToString();
                    di.Name = i + "固定值";
                    di.Value = (month * j * 50 + i).ToString("F2");
                    ds.DataItems.Add(di);
                }
                retDr.DataSeries.Add(ds);
            }
            return retDr;
        }

        private DataResult RandomData3(List<QueryParameter> parameters)
        {
            int rownum = int.Parse(parameters.Find(p => p.Name.Equals("rownum")).ValueString);
            int colnum = int.Parse(parameters.Find(p => p.Name.Equals("colnum")).ValueString);
            Random r = new Random();
            DataResult retDr = new DataResult();
            retDr.Name = "random";
            retDr.DataSeries = new List<DataSeries>();
            for (int j = 1; j <= rownum; j++)
            {
                DataSeries ds = new DataSeries();
                ds.DataItems = new List<DataItem>();
                ds.Id = ((char)(64 + j)).ToString();
                ds.Name = "测试数据" + j;
                for (int i = 1; i <= colnum; i++)
                {
                    DataItem di = new DataItem();
                    di.Id = i.ToString();
                    di.Name = i + "随机数据";
                    di.Value = (r.NextDouble() * 50 + 75).ToString("F2");
                    ds.DataItems.Add(di);
                }
                retDr.DataSeries.Add(ds);
            }
            return retDr;
        }

        private DataResult RandomData2()
        {
            Random r = new Random();
            DataResult retDr = new DataResult();
            retDr.Name = "random";
            retDr.DataSeries = new List<DataSeries>();
            for (int j = 1; j <= 2; j++)
            {
                DataSeries ds = new DataSeries();
                ds.DataItems = new List<DataItem>();
                ds.Id = ((char)(64 + j)).ToString();
                ds.Name = "B" + j;
                for (int i = 1; i <= 12; i++)
                {
                    DataItem di = new DataItem();
                    di.Id = i.ToString();
                    di.Name = i + "月";
                    di.Value = (r.Next(50) + 75).ToString();
                    ds.DataItems.Add(di);
                }
                retDr.DataSeries.Add(ds);
            }
            return retDr;
        }

        public DataResult RandomData()
        {
            Random r = new Random();
            DataResult retDr = new DataResult();
            retDr.Name = "random";
            retDr.DataSeries = new List<DataSeries>();
            DataSeries ds = new DataSeries();
            ds.DataItems = new List<DataItem>();
            ds.Id = "A";
            ds.Name = "B";
            for (int i = 1; i <= 12; i++)
            {
                DataItem di = new DataItem();
                di.Id = i.ToString();
                di.Name = i + "月";
                di.Value = (r.Next(50) + 75).ToString();
                ds.DataItems.Add(di);
            }
            retDr.DataSeries.Add(ds);
            return retDr;
        }

        /// <summary>
        /// a2.6 物探队伍情况数据
        /// </summary>
        private DataResult RandomDataA26Left2(List<QueryParameter> parameters)
        {
            int rownum = int.Parse(parameters.Find(p => p.Name.Equals("rownum")).ValueString);
            int colnum = int.Parse(parameters.Find(p => p.Name.Equals("colnum")).ValueString);
            Random r = new Random();
            DataResult retDr = new DataResult();
            retDr.Name = "random";
            retDr.DataSeries = new List<DataSeries>();
            for (int j = 1; j <= rownum; j++)
            {
                DataSeries ds = new DataSeries();
                ds.DataItems = new List<DataItem>();
                ds.Id = ((char)(64 + j)).ToString();
                ds.Name = "测试数据" + j;
                for (int i = 1; i <= colnum; i++)
                {
                    DataItem di = new DataItem();
                    di.Id = i.ToString();
                    di.Name = i + "随机数据";
                    if (i == 1)
                    {
                        di.Value = LoadA26Left1NameData()[j - 1].ToString();
                    }
                    else
                    {
                        di.Value = (r.NextDouble() * 50 + 75).ToString("F2");
                    }
                    ds.DataItems.Add(di);
                }
                retDr.DataSeries.Add(ds);
            }
            return retDr;
        }

        /// <summary>
        /// a2.6 测井、录井、井下作业队伍情况数据
        /// </summary>
        private DataResult RandomDataA26Left2Dowm(List<QueryParameter> parameters)
        {
            int rownum = int.Parse(parameters.Find(p => p.Name.Equals("rownum")).ValueString);
            int colnum = int.Parse(parameters.Find(p => p.Name.Equals("colnum")).ValueString);
            Random r = new Random();
            DataResult retDr = new DataResult();
            retDr.Name = "random";
            retDr.DataSeries = new List<DataSeries>();
            for (int j = 1; j <= rownum; j++)
            {
                DataSeries ds = new DataSeries();
                ds.DataItems = new List<DataItem>();
                ds.Id = ((char)(64 + j)).ToString();
                ds.Name = "测试数据" + j;
                for (int i = 1; i <= colnum; i++)
                {
                    DataItem di = new DataItem();
                    di.Id = i.ToString();
                    di.Name = i + "随机数据";
                    if (i == 1)
                    {
                        di.Value = LoadA26Left2NameData()[j - 1].ToString();
                    }
                    else
                    {
                        di.Value = (r.NextDouble() * 50 + 75).ToString("F2");
                    }
                    ds.DataItems.Add(di);
                }
                retDr.DataSeries.Add(ds);
            }
            return retDr;
        }
             /// <summary>
        /// a2.6 钻井队伍情况数据
        /// </summary>
        private DataResult RandomDataA26Left2Up(List<QueryParameter> parameters)
        {
            int rownum = int.Parse(parameters.Find(p => p.Name.Equals("rownum")).ValueString);
            int colnum = int.Parse(parameters.Find(p => p.Name.Equals("colnum")).ValueString);
            Random r = new Random();
            DataResult retDr = new DataResult();
            retDr.Name = "random";
            retDr.DataSeries = new List<DataSeries>();
            for (int j = 1; j <= rownum; j++)
            {
                DataSeries ds = new DataSeries();
                ds.DataItems = new List<DataItem>();
                ds.Id = ((char)(64 + j)).ToString();
                ds.Name = "测试数据" + j;
                for (int i = 1; i <= colnum; i++)
                {
                    DataItem di = new DataItem();
                    di.Id = i.ToString();
                    di.Name = i + "随机数据";
                    if (i == 1)
                    {
                        di.Value = LoadA26Left1MiddleNameData()[j - 1].ToString();
                    }
                    else
                    {
                        di.Value = (r.NextDouble() * 50 + 75).ToString("F2");
                    }
                    ds.DataItems.Add(di);
                }
                retDr.DataSeries.Add(ds);
            }
            return retDr;
        }

        /// <summary>
        /// a2.6 A3各油气田作业情况
        /// </summary>
        /// <param name="parameters"></param>
        /// <returns></returns>
        private DataResult RandomDataA26A3NameData(List<QueryParameter> parameters)
        {
            int rownum = int.Parse(parameters.Find(p => p.Name.Equals("rownum")).ValueString);
            int colnum = int.Parse(parameters.Find(p => p.Name.Equals("colnum")).ValueString);
            Random r = new Random();
            DataResult retDr = new DataResult();
            retDr.Name = "random";
            retDr.DataSeries = new List<DataSeries>();
            for (int j = 1; j <= rownum; j++)
            {
                DataSeries ds = new DataSeries();
                ds.DataItems = new List<DataItem>();
                ds.Id = ((char)(64 + j)).ToString();
                ds.Name = "测试数据" + j;
                for (int i = 1; i <= colnum; i++)
                {
                    DataItem di = new DataItem();
                    di.Id = i.ToString();
                    di.Name = i + "随机数据";
                    if (i == 1)
                    {
                        di.Value = LoadA26A3NameData()[j - 1].ToString();
                    }
                    else
                    {
                        di.Value = (r.NextDouble() * 50 + 75).ToString("F2");
                    }
                    ds.DataItems.Add(di);
                }
                retDr.DataSeries.Add(ds);
            }
            return retDr;
        }

        /// <summary>
        /// a2.6 A4重点井作业情况
        /// </summary>
        /// <param name="parameters"></param>
        /// <returns></returns>
        private DataResult RandomDataA26A4NameData(List<QueryParameter> parameters)
        {
            int rownum = int.Parse(parameters.Find(p => p.Name.Equals("rownum")).ValueString);
            int colnum = int.Parse(parameters.Find(p => p.Name.Equals("colnum")).ValueString);
            Random r = new Random();
            DataResult retDr = new DataResult();
            retDr.Name = "random";
            retDr.DataSeries = new List<DataSeries>();
            for (int j = 1; j <= rownum; j++)
            {
                DataSeries ds = new DataSeries();
                ds.DataItems = new List<DataItem>();
                ds.Id = ((char)(64 + j)).ToString();
                ds.Name = "测试数据" + j;
                for (int i = 1; i <= colnum; i++)
                {
                    DataItem di = new DataItem();
                    di.Id = i.ToString();
                    di.Name = i + "随机数据";
                    if (i == 1)
                    {
                        di.Value = LoadA26A4NameData()[j - 1].ToString();
                    }
                    else
                    {
                        di.Value = (r.NextDouble() * 50 + 75).ToString("F2");
                    }
                    ds.DataItems.Add(di);
                }
                retDr.DataSeries.Add(ds);
            }
            return retDr;
        }


        /// <summary>
        /// 钻井队伍情况
        /// </summary>
        /// <returns></returns>
        private List<string> LoadA26Left1MiddleNameData()
        {
            List<string> nameDatas = new List<string>();
            nameDatas.Add("大庆钻探");
            nameDatas.Add("西部钻探");
            nameDatas.Add("长城钻探");
            nameDatas.Add("渤海钻探");
            nameDatas.Add("川庆钻探");
            nameDatas.Add("海洋工程");
            return nameDatas;
        }
     
        /// <summary>
        /// 物探队伍情况
        /// </summary>
        /// <returns></returns>
        private List<string> LoadA26Left1NameData()
        {
            List<string> nameDatas = new List<string>();
            nameDatas.Add("东方物探");
            nameDatas.Add("大庆钻探");
            nameDatas.Add("川庆钻探");

            return nameDatas;
        }

        /// <summary>
        /// 录井、测井、井下作业队伍情况
        /// </summary>
        /// <returns></returns>
        private List<string> LoadA26Left2NameData()
        {
            List<string> nameDatas = new List<string>();
            nameDatas.Add("大庆钻探");
            nameDatas.Add("西部钻探");
            nameDatas.Add("长城钻探");
            nameDatas.Add("渤海钻探");
            nameDatas.Add("川庆钻探");
            nameDatas.Add("测井公司");
            return nameDatas;
        }

        /// <summary>
        /// a2.6 A3各油气田作业情况
        /// </summary>
        /// <returns></returns>
        private List<string> LoadA26A3NameData()
        {
            List<string> nameDatas = new List<string>();
            nameDatas.Add("大庆");
            nameDatas.Add("吉林");
            nameDatas.Add("辽河");
            nameDatas.Add("华北");
            nameDatas.Add("大港");
            nameDatas.Add("冀东");
            nameDatas.Add("浙江");
            nameDatas.Add("新疆");
            nameDatas.Add("塔里木");
            nameDatas.Add("吐哈");
            nameDatas.Add("长庆");
            nameDatas.Add("青海");
            nameDatas.Add("玉门");
            nameDatas.Add("西南");
            nameDatas.Add("南方");
            return nameDatas;
        }

        /// <summary>
        /// 重点井工作情况
        /// </summary>
        /// <returns></returns>
        private List<string> LoadA26A4NameData()
        {
            List<string> nameDatas = new List<string>();
            nameDatas.Add("塔里木");
            nameDatas.Add("塔里木");
            nameDatas.Add("塔里木");
            nameDatas.Add("塔里木");
            nameDatas.Add("塔里木");
            nameDatas.Add("塔里木");
            nameDatas.Add("塔里木");
            nameDatas.Add("西南");
            nameDatas.Add("西南");
            nameDatas.Add("大港");
            nameDatas.Add("塔里木");
            nameDatas.Add("西南");
            nameDatas.Add("西南");
            nameDatas.Add("西南");
            nameDatas.Add("西南");
            nameDatas.Add("大港");
            nameDatas.Add("冀东");
            nameDatas.Add("吐哈");
            nameDatas.Add("大庆");
            nameDatas.Add("辽河");
            return nameDatas;
        }

        /// <summary>
        /// C4.2  S3S42A2装置检修信息数据
        /// </summary>
        /// <param name="parameters"></param>
        /// <returns></returns>
        private DataResult RandomDataC42S3S42A2NameData(List<QueryParameter> parameters)
        {
            int rownum = int.Parse(parameters.Find(p => p.Name.Equals("rownum")).ValueString);
            int colnum = int.Parse(parameters.Find(p => p.Name.Equals("colnum")).ValueString);
            Random r = new Random();
            DataResult retDr = new DataResult();
            retDr.Name = "random";
            retDr.DataSeries = new List<DataSeries>();
            for (int j = 1; j <= rownum; j++)
            {
                DataSeries ds = new DataSeries();
                ds.DataItems = new List<DataItem>();
                ds.Id = "_" + j;
                ds.Name = "测试数据" + j;
                for (int i = 1; i <= colnum; i++)
                {
                    DataItem di = new DataItem();
                    di.Id ="_"+ i.ToString();
                    di.Name = i + "随机数据";
                    if (i == 1)
                    {
                        di.Value = LoadC42S3S42A2NameData()[j - 1].ToString();
                    }
                    else if (i == 2)
                    {
                        di.Value = LoadC42S3S42A2NameData2()[j - 1].ToString();
                    }
                    else
                    {
                        di.Value = DateTime.Now.ToString();
                    }
                    ds.DataItems.Add(di);
                }
                retDr.DataSeries.Add(ds);
            }
            return retDr;
        }

        private List<string> LoadC42S3S42A2NameData()
        {
            List<string> nameDatas = new List<string>();
            nameDatas.Add("300万吨常减压装置");
            nameDatas.Add("60万吨乙烯裂解装置");
            nameDatas.Add("100万吨催化裂化装置");
            nameDatas.Add("100万吨汽油加氢脱硫装置");
            nameDatas.Add("20万吨合成氨装置");
            return nameDatas;
        }

        private List<string> LoadC42S3S42A2NameData2()
        {
            List<string> nameDatas = new List<string>();
            nameDatas.Add("计划");
            nameDatas.Add("计划");
            nameDatas.Add("非计划");
            nameDatas.Add("计划");
            nameDatas.Add("非计划");
            return nameDatas;
        }
    
    }
}
